package com.gdk.ksoft.assembly.dbhelper;

import com.gdk.ksoft.data.entry.tables.ShoppingCartGoodsTable;
import com.gdk.ksoft.data.entry.tables.UnsoldGoodsTable;
import com.gdk.ksoft.assembly.utils.extensions.FormatUtils;
import com.lidroid.xutils.DbUtils;
import com.lidroid.xutils.db.sqlite.Selector;
import com.lidroid.xutils.db.sqlite.SqlInfo;
import com.lidroid.xutils.db.table.DbModel;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by Mrper on 2014/12/26.
 * 促销数据数据库操作辅助类
 */
public class PsaleDbHelper {

    /**
     * 查询所有的买赠本品的商品
     * @param saleId
     * @return
     * @throws Exception
     */
    public static List<ShoppingCartGoodsTable> findAllBuySaleGoods(DbUtils dbUtils,int kh_id,int shdz_id,int saleId)throws Exception{
        String sql = "SELECT DISTINCT b.cx_id,c.id,c.mc,c.slt,c.gg,c.tm,c.dw,d.jldwmc,d.dj,d.hsgx,c.csrq,c.bzq " +
                "FROM saleself a,saleself_goods b,shangpin c,shangpin_jldw d " +
                "WHERE a.id = b.cx_id AND c.id = b.sp_id AND c.id = d.sp_id AND a.id = "+saleId+";";
        List<DbModel> list = dbUtils.findDbModelAll(new SqlInfo(sql));
        List<ShoppingCartGoodsTable> carlist = null;
        if(list!=null && list.size()>0){
            carlist = new ArrayList<>();
            for(int i=0;i<list.size();i++){
                final DbModel data = list.get(i);
                ShoppingCartGoodsTable item = new ShoppingCartGoodsTable();
                item.kh_id = kh_id;
                item.shdz_id = shdz_id;
                item.sp_id = data.getInt("id");
                item.tm = data.getString("tm");
                item.xgbs = 0;//默认不可修改
                item.xsdw = data.getString("jldwmc");
                item.xsdwsl = 0;
                item.xsdwss = 0;
                item.xsyj = data.getFloat("dj");
                item.yfje = 0;
                item.zdsj = FormatUtils.standardDatetime(new Date(),"yyyy-MM-dd HH:mm:ss");
                item.bzq = data.getInt("bzq");
                item.cx_bz = "";
                if(item.bzq!=0){//查询保质期数据
                    UnsoldGoodsTable unsoldInfo = dbUtils.findFirst(Selector.from(UnsoldGoodsTable.class)
                            .where("sp_id","=",item.sp_id).orderBy("dlrq"));
                    if(unsoldInfo != null) {//修改下面的参数
                        item.bzq = unsoldInfo.bzts;//修改保质期天数数据
                        item.zdsj = unsoldInfo.csrq;//获取其生成日期
                    }
                }
                item.bz = "";
                item.bzlsj = data.getFloat("dj");
                item.bzxsdwj = data.getFloat("dj");
                item.gg = data.getString("gg");
                item.hsdw = data.getString("dw");
                item.hsdwsl = 0;
                item.hsgx = data.getInt("hsgx");
                item.lx = 9;//默认买赠本品
                item.mc = data.getString("mc");
                item.slt = data.getString("slt");
                item.mztp_id = data.getInt("cx_id");
                item.bpsp_id = data.getInt("cx_id");
                carlist.add(item);
            }
        }
        return carlist;
    }

    /**
     * 查询所有的买赠它品的商品
     * @param saleId
     * @return
     * @throws Exception
     */
    public static List<ShoppingCartGoodsTable> findAllBuyOtherGoods(DbUtils dbUtils,int kh_id,int shdz_id,int saleId)throws Exception{
        String sql = "SELECT DISTINCT b.cx_id,c.id,c.mc,c.slt,c.gg,c.tm,c.dw,d.jldwmc,d.dj,d.hsgx,c.csrq,c.bzq " +
                "FROM saleother a,saleother_goods b,shangpin c,shangpin_jldw d " +
                "WHERE a.id = b.cx_id AND c.id = b.sp_id AND c.id = d.sp_id AND a.id = "+saleId+";";
        List<DbModel> list = dbUtils.findDbModelAll(new SqlInfo(sql));
        List<ShoppingCartGoodsTable> carlist = null;
        if(list!=null && list.size()>0){
            carlist = new ArrayList<>();
            for(int i=0;i<list.size();i++){
                final DbModel data = list.get(i);
                ShoppingCartGoodsTable item = new ShoppingCartGoodsTable();
                item.kh_id = kh_id;
                item.shdz_id = shdz_id;
                item.sp_id = data.getInt("id");
                item.tm = data.getString("tm");
                item.xgbs = 0;//默认不可修改
                item.xsdw = data.getString("jldwmc");
                item.xsdwsl = 0;
                item.xsdwss = 0;
                item.xsyj = data.getFloat("dj");
                item.yfje = 0;
                item.zdsj = FormatUtils.standardDatetime(new Date(),"yyyy-MM-dd HH:mm:ss");
                item.bzq = data.getInt("bzq");
                if(item.bzq!=0){//查询保质期数据
                    UnsoldGoodsTable unsoldInfo = dbUtils.findFirst(Selector.from(UnsoldGoodsTable.class)
                            .where("sp_id","=",item.sp_id).orderBy("dlrq"));
                    if(unsoldInfo != null) {//修改下面的参数
                        item.bzq = unsoldInfo.bzts;//修改保质期天数数据
                        item.zdsj = unsoldInfo.csrq;//获取其生成日期
                    }
                }
                item.bz = "";
                item.bzlsj = data.getFloat("dj");
                item.bzxsdwj = data.getFloat("dj");
                item.gg = data.getString("gg");
                item.hsdw = data.getString("dw");
                item.hsdwsl = 0;
                item.hsgx = data.getInt("hsgx");
                item.lx = 9;//默认买赠本品
                item.mc = data.getString("mc");
                item.slt = data.getString("slt");
                item.mzbp_id = data.getInt("cx_id");
                item.bpsp_id = data.getInt("cx_id");

                carlist.add(item);
            }
        }
        return carlist;
    }

    /**
     * 查询所有的买赠它品的赠送商品
     * @param saleId
     * @return
     * @throws Exception
     */
    public static List<ShoppingCartGoodsTable> findAllBuyOtherGiftGoods(DbUtils dbUtils,int kh_id,int shdz_id,int saleId)throws Exception{
        String sql = "SELECT DISTINCT b.cx_id,c.id,c.mc,c.slt,c.gg,c.tm,c.dw,d.jldwmc,d.dj,d.hsgx,c.csrq,c.bzq,b.mztp_mzjt_id " +
                "FROM saleother a,saleother_gift b,shangpin c,shangpin_jldw d " +
                "WHERE a.id = b.cx_id AND c.id = b.sp_id AND c.id = d.sp_id AND a.id = "+saleId+";";
        List<DbModel> list = dbUtils.findDbModelAll(new SqlInfo(sql));
        List<ShoppingCartGoodsTable> carlist = null;
        if(list!=null && list.size()>0){
            carlist = new ArrayList<>();
            for(int i=0;i<list.size();i++){
                final DbModel data = list.get(i);
                ShoppingCartGoodsTable item = new ShoppingCartGoodsTable();
                item.kh_id = kh_id;
                item.shdz_id = shdz_id;
                item.sp_id = data.getInt("id");
                item.tm = data.getString("tm");
                item.xgbs = 0;//默认不可修改
                item.xsdw = data.getString("jldwmc");
                item.xsdwsl = 0;
                item.xsdwss = 0;
                item.xsyj = data.getFloat("dj");
                item.yfje = 0;
                item.zdsj = FormatUtils.standardDatetime(new Date(),"yyyy-MM-dd HH:mm:ss");
                item.bzq = data.getInt("bzq");
                if(item.bzq!=0){//查询保质期数据
                    UnsoldGoodsTable unsoldInfo = dbUtils.findFirst(Selector.from(UnsoldGoodsTable.class)
                            .where("sp_id","=",item.sp_id).orderBy("dlrq"));
                    if(unsoldInfo != null) {//修改下面的参数
                        item.bzq = unsoldInfo.bzts;//修改保质期天数数据
                        item.zdsj = unsoldInfo.csrq;//获取其生成日期
                    }
                }
                item.bz = "";
                item.bzlsj = data.getFloat("dj");
                item.bzxsdwj = data.getFloat("dj");
                item.gg = data.getString("gg");
                item.hsdw = data.getString("dw");
                item.hsdwsl = 0;
                item.hsgx = data.getInt("hsgx");
                item.lx = 9;//默认买赠本品
                item.mc = data.getString("mc");
                item.slt = data.getString("slt");
                item.mzbp_id = data.getInt("cx_id");
                item.bpsp_id = data.getInt("cx_id");
//                data.getInt("mztp_mzjt_id");  //买赠它品阶梯ID
                carlist.add(item);
            }
        }
        return carlist;
    }

}
